﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using System.Data;

namespace PersonDetails
{
    class DbInteraction
    {
        private MySqlConnection connection;
        private String server;
        private String database;
        private String uid;
        private String password;

        string MyConnectionString = "Server=localhost;Database=mydb;Uid=root;Pwd=Kiran123";

        public DbInteraction()
        {
            Initialize();
        }

        private void Initialize()
        {
            server = "localhost";
            database = "mydb";
            uid = "root";
            password = "Kiran123";
            String ConnectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
            connection = new MySqlConnection(ConnectionString);
        }

        //open connection to database
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;

                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        //Close Connection
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        //Insert statement
        public void Insert(PersonDetails details)
        {
            string query = "INSERT INTO customer(first_name, last_name, email, address_id, middle_name, DOB) VALUES(@first_name, @last_name, @email, @address_id, @middle_name, @DOB)";

            //open connection
            if (this.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //cmd.Parameters.AddWithValue("@Customer_ID", details.CustomerId);
                cmd.Parameters.AddWithValue("@first_name", details.FirstName);
                cmd.Parameters.AddWithValue("@last_name", details.LastName);
                cmd.Parameters.AddWithValue("@email", details.email);
                cmd.Parameters.AddWithValue("@address_id", details.addressid);
                cmd.Parameters.AddWithValue("@middle_name", details.MiddleName);
                cmd.Parameters.AddWithValue("@DOB", details.dateOfBirth);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

        //Update statement
        public void Update(PersonDetails details)
        {
            string query = "UPDATE customer SET first_name=@first_name, last_name=@last_name, email=@email, address_id=@address_id, middle_name=@middle_name, DOB=@DOB WHERE Customer_ID=" + details.CustomerId.ToString();

            //Open connection
            if (this.OpenConnection() == true)
            {
                //create mysql command
                MySqlCommand cmd = new MySqlCommand();
                //Assign the query using CommandText
                cmd.CommandText = query;
                //Assign the connection using Connection
                cmd.Connection = connection;

                //cmd.Parameters.AddWithValue("@Customer_ID", details.CustomerId);
                cmd.Parameters.AddWithValue("@first_name", details.FirstName);
                cmd.Parameters.AddWithValue("@last_name", details.LastName);
                cmd.Parameters.AddWithValue("@email", details.email);
                cmd.Parameters.AddWithValue("@address_id", details.addressid);
                cmd.Parameters.AddWithValue("@middle_name", details.MiddleName);
                cmd.Parameters.AddWithValue("@DOB", details.dateOfBirth);

                //Execute query
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

        //Delete Statement
        public void Delete(int CustomerId)
        {
            string query = "DELETE FROM customer WHERE Customer_ID = @Customer_ID";

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.Parameters.AddWithValue("@Customer_ID", CustomerId);

                cmd.ExecuteNonQuery();
                this.CloseConnection();
            }
        }

        public DataSet select()
        {
            DataSet dtSet = new DataSet();

            string query = "SELECT * FROM customer";

            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);

                dataAdapter.Fill(dtSet);

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return dtSet;
            }
            else
            {
                return dtSet;
            }
        }

        //Count statement
        public int Count()
        {
            string query = "SELECT Count(*) FROM customer";
            int Count = -1;

            //Open Connection
            if (this.OpenConnection() == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                //close Connection
                this.CloseConnection();

                return Count;
            }
            else
            {
                return Count;
            }
        }

    }
}
